package com.newviews.tcmip.common.utils;

import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ChenLong
 * @create 2021-11-07 5:28
 */
@Slf4j
public class MySqlScriptUtils {


    /**
     * 生成mysql批量插入的sql语句
     *
     * @param tableName 表名
     * @param colList   列名
     * @param fieldList 字段名
     * @param rows      Data
     * @return String sql
     */
    public static String generateInsertBatchSql(String tableName, List<String> colList, List<String> colTypeList, List<String> fieldList, List<Map<String, Object>> rows) {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(tableName).append("(");
        boolean isAppendComma = false;
        for (String col : colList) {
            if (!isAppendComma) {
                sb.append("`").append(col).append("`");
                isAppendComma = true;
            } else {
                sb.append(",`").append(col).append("`");
            }
        }
        sb.append(")").append(" values ");
        int i = 0;
        for (Map<String, Object> rowData : rows) {
            i++;
            sb.append("(");

            int j = 0;
            for (String field : fieldList) {
                j++;
                // 这里可以判断value的类型来决定怎么生成
                // 当前默认为string类型,未做特殊处理
                if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    sb.append("null");
                } else {
                    sb.append("'").append((rowData.get(field).toString()).replace("'", "").replace("\\", "\\\\")).append("'");
                }
                if (j != fieldList.size()) {
                    sb.append(",");
                }
            }
            sb.append(")");

            if (i != rows.size()) {
                sb.append(",");
            }
        }

        return sb.toString();
    }

    /**
     * 生成mysql插入的sql语句
     */
    public static String generateInsertSql(String tableName, List<String> colList, List<String> colTypeList, List<String> fieldList, Map<String, Object> rowData) {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(tableName).append("(");
        boolean isAppendComma = false;
        for (String col : colList) {
            if (!isAppendComma) {
//                sb.append("`").append(col).append("`");
                sb.append(col);
                isAppendComma = true;
            } else {
                //sb.append(",`").append(col).append("`");
                sb.append(",").append(col);
            }
        }
        sb.append(")").append(" values ").append("(");
        int j = 0;
        for (String field : fieldList) {
            j++;
            String colType = colTypeList.get(j - 1);
            log.debug("colType:{},field:{},value:{}", colType, field, String.valueOf(rowData.get(field)));
            if ("smallint".equalsIgnoreCase(colType) || "tinyint".equalsIgnoreCase(colType) || "int".equalsIgnoreCase(colType)) {
                if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    sb.append("null");
                } else {
                    sb.append(rowData.get(field).toString().trim());
                }
            } else if ("binary".equalsIgnoreCase(colType) || "varbinary".equalsIgnoreCase(colType)) {
                sb.append("null");
            } else if ("decimal".equalsIgnoreCase(colType)) {
                if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    sb.append("null");
                } else {
                    sb.append(rowData.get(field).toString().trim());
                }
            } else if ("numeric".equalsIgnoreCase(colType)) {
                if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    sb.append("null");
                } else {
                    sb.append(rowData.get(field).toString().trim());
                }
            } else if ("money".equalsIgnoreCase(colType)) {
                if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    sb.append("null");
                } else {
                    sb.append(rowData.get(field).toString().trim());
                }
            } else if ("datetime".equalsIgnoreCase(colType)) {
                if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    sb.append("null");
                } else {
                    sb.append("'").append((rowData.get(field).toString().trim()).replace("'", "")).append("'");
                }
            } else if ("bit".equalsIgnoreCase(colType)) {
                if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    sb.append("null");
                } else {
                    sb.append(rowData.get(field).toString().trim().equalsIgnoreCase("false") ? "0" : "1");
                }
            } else {
                if (rowData.get(field) == null || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    sb.append("null");
                } else if ("".equals(rowData.get(field).toString().trim())) {
                    sb.append("''");
                } else {
                    sb.append("'").append((rowData.get(field).toString().trim()).replace("'", "").replace("\\", "\\\\")).append("'");
                }
            }

            if (j != fieldList.size()) {
                sb.append(",");
            }
        }
        sb.append(")");


        return sb.toString();
    }


    public static String generateInsertBatchSql(String sql,String tableName, List<String> colList, List<String> colTypeList, List<String> fieldList, Map<String, Object> rowData) {
        StringBuilder sb = new StringBuilder(sql);
        if(sql==""){
            sb.append("insert into ").append(tableName).append("(");
            boolean isAppendComma = false;
            for (String col : colList) {
                if (!isAppendComma) {
//                sb.append("`").append(col).append("`");
                    sb.append(col);
                    isAppendComma = true;
                } else {
                    //sb.append(",`").append(col).append("`");
                    sb.append(",").append(col);
                }
            }
            sb.append(")").append(" values ").append("(");
            int j = 0;
            for (String field : fieldList) {
                j++;
                String colType = colTypeList.get(j - 1);
                log.debug("colType:{},field:{},value:{}", colType, field, String.valueOf(rowData.get(field)));
                if ("smallint".equalsIgnoreCase(colType) || "tinyint".equalsIgnoreCase(colType) || "int".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim());
                    }
                } else if ("binary".equalsIgnoreCase(colType) || "varbinary".equalsIgnoreCase(colType)) {
                    sb.append("null");
                } else if ("decimal".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim());
                    }
                } else if ("numeric".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim());
                    }
                } else if ("money".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim());
                    }
                } else if ("datetime".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append("'").append((rowData.get(field).toString().trim()).replace("'", "")).append("'");
                    }
                } else if ("bit".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim().equalsIgnoreCase("false") ? "0" : "1");
                    }
                } else {
                    if (rowData.get(field) == null || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else if ("".equals(rowData.get(field).toString().trim())) {
                        sb.append("''");
                    } else {
                        sb.append("'").append((rowData.get(field).toString().trim()).replace("'", "").replace("\\", "\\\\")).append("'");
                    }
                }

                if (j != fieldList.size()) {
                    sb.append(",");
                }
            }
            sb.append(")");

        }else {
            sb.append(",(");
            int j = 0;
            for (String field : fieldList) {
                j++;
                String colType = colTypeList.get(j - 1);
                log.debug("colType:{},field:{},value:{}", colType, field, String.valueOf(rowData.get(field)));
                if ("smallint".equalsIgnoreCase(colType) || "tinyint".equalsIgnoreCase(colType) || "int".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim());
                    }
                } else if ("binary".equalsIgnoreCase(colType) || "varbinary".equalsIgnoreCase(colType)) {
                    sb.append("null");
                } else if ("decimal".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim());
                    }
                } else if ("numeric".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim());
                    }
                } else if ("money".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim());
                    }
                } else if ("datetime".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append("'").append((rowData.get(field).toString().trim()).replace("'", "")).append("'");
                    }
                } else if ("bit".equalsIgnoreCase(colType)) {
                    if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else {
                        sb.append(rowData.get(field).toString().trim().equalsIgnoreCase("false") ? "0" : "1");
                    }
                } else {
                    if (rowData.get(field) == null || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                        sb.append("null");
                    } else if ("".equals(rowData.get(field).toString().trim())) {
                        sb.append("''");
                    } else {
                        sb.append("'").append((rowData.get(field).toString().trim()).replace("'", "").replace("\\", "\\\\")).append("'");
                    }
                }

                if (j != fieldList.size()) {
                    sb.append(",");
                }
            }
            sb.append(")");
        }
        return sb.toString();
    }
    /**
     * 生成mysql修改的sql语句
     */
    public static String generateUpdateSql(String tableName, List<String> colList, List<String> colTypeList, List<String> fieldList, Map<String, Object> rowData, String wh) {
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(tableName).append(" set ");
        int j = 0;
        for (String field : fieldList) {
            j++;
            String v;
            String colType = colTypeList.get(j - 1);
            if ("smallint".equalsIgnoreCase(colType) || "tinyint".equalsIgnoreCase(colType) || "int".equalsIgnoreCase(colType)) {
                v = (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) ? null : rowData.get(field).toString().trim();
            } else if ("binary".equalsIgnoreCase(colType) || "varbinary".equalsIgnoreCase(colType)) {
                v = null;
            } else if ("decimal".equalsIgnoreCase(colType)) {
                v = (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) ? null : rowData.get(field).toString().trim();
            } else if ("numeric".equalsIgnoreCase(colType)) {
                v = (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) ? null : rowData.get(field).toString().trim();
            } else if ("money".equalsIgnoreCase(colType)) {
                v = (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) ? null : rowData.get(field).toString().trim();
            } else if ("datetime".equalsIgnoreCase(colType)) {
                v = (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) ? null : String.format("'%s'", (rowData.get(field).toString().trim()).replace("'", "").replace("\\", "\\\\"));
            } else if ("bit".equalsIgnoreCase(colType)) {
                if (rowData.get(field) == null || "".equals(rowData.get(field).toString().trim()) || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    v = null;
                } else if (rowData.get(field).toString().trim().equalsIgnoreCase("false")) {
                    v = "0";
                } else {
                    v = "1";
                }
            } else {
                if (rowData.get(field) == null || "null".equalsIgnoreCase(rowData.get(field).toString().trim())) {
                    v = null;
                } else if ("".equals(rowData.get(field).toString().trim())) {
                    v = "''";
                } else {
                    v = String.format("'%s'", (rowData.get(field).toString().trim()).replace("'", "").replace("\\", "\\\\"));
                }
            }
            if (j != 1) {
                sb.append(",");
            }
            sb.append(String.format("%s = %s", colList.get(j - 1), v));
        }
        sb.append(wh);
        return sb.toString();
    }

    public static void main(String[] args) {
        String tableName = "data_source_v1.z_cisdb_cpoe_brsyk";
        List<String> colList = new ArrayList<>();
        colList.add("EMRXH");
        colList.add("SYXH");
        Map<String, Object> rowData = new HashMap<>();
        rowData.put("EMRXH", 11231);
        rowData.put("SYXH", 1231);

        String wh = " where EMRXH = 123";
        System.out.println(generateUpdateSql(tableName, colList, colList, colList, rowData, wh));

    }
}


